using System;
using System.Collections.Generic;
using System.Text;
using GAS.Common.DataSets;
using GAS.Common.DbAccess;
using System.Configuration;
using System.Data;
using GAS.Common.Constants;
using System.IO;

namespace GAS.Extractors.Oracle
{
	public class OracleExtractor
	{

		public void Extract()
		{
			string auditGuid;
			string[] auditOptionsArray;
			List<string> auditOptions;
			AuditDataSet auditDS;
			bool noWebService;

			noWebService = bool.Parse(ConfigurationManager.AppSettings["NoWebService"]);

			if (noWebService)
			{
				string[] splittedAuditOptions;

				auditOptions = new List<string>();
				splittedAuditOptions = ConfigurationManager.AppSettings["AuditOptions"].Split(',');
				foreach (string splittedAuditOption in splittedAuditOptions)
				{
					auditOptions.Add(splittedAuditOption.Trim());
				}

				Console.Write("Generating audit with auditOptions: ");
				int i = 0;
				foreach (string auditOption in auditOptions)
				{
					Console.Write(auditOption);
					if (i < auditOptions.Count - 1)
						Console.Write(", ");
				}
				Console.WriteLine();

				auditDS = GenerateAuditDataSet(auditOptions);

				Console.WriteLine("Audit DataSet generated");

				WriteXmlFile(auditDS);
			}
			else
			{
				GASWebServices.GASWebService webService;
				webService = new GASWebServices.GASWebService();

				int timeout = 300000;
				try
				{
					timeout = int.Parse(ConfigurationManager.AppSettings["webServiceTimeout"]);
				}
				catch
				{
				}
				webService.Timeout = timeout;


				Console.WriteLine("Web service generated with URL: " + webService.Url);

				auditGuid = webService.GetLastAuditCode();

				Console.WriteLine("Audit Code: " + auditGuid);

				auditOptionsArray = webService.GetAuditOptions(auditGuid);
				auditOptions = new List<string>(auditOptionsArray);

				Console.Write("Generating audit with auditOptions: ");
				int i = 0;
				foreach (string auditOption in auditOptions)
				{
					Console.Write(auditOption);
					if (i < auditOptions.Count - 1)
						Console.Write(", ");
					i++;
				}
				Console.WriteLine();

				auditDS = GenerateAuditDataSet(auditOptions);

				Console.WriteLine("Audit DataSet generated");

				bool dump = false;
				try
				{
					dump = bool.Parse(ConfigurationManager.AppSettings["dump"]);
				}
				catch
				{
				}

				if (dump)
					auditDS.WriteXml("dump.xml", XmlWriteMode.IgnoreSchema);

				try
				{
					webService.SaveHostAudit(auditDS, auditGuid);

					Console.WriteLine("Audit DataSet sent to web service");
				}
				catch (Exception ex)
				{
					Console.WriteLine("Exception while saving audit: " + ex.Message);
				}
			}
		}

		private void WriteXmlFile(AuditDataSet auditDS)
		{
			string fileName;
			DateTime now = DateTime.Now;
			int i;

			fileName = "audit_" + now.Year.ToString() + "." + now.Month.ToString() + "." + now.Day.ToString() + "_";

			i = 0;
			fileName = fileName + "0";
			while (File.Exists(fileName + ".xml"))
			{
				i++;
				fileName = fileName.Substring(0, fileName.LastIndexOf("_") + 1) + i;
			}
			auditDS.WriteXml(fileName + ".xml", XmlWriteMode.IgnoreSchema);
		}

		private AuditDataSet GenerateAuditDataSet(List<string> auditOptions)
		{
			AuditDataSet auditDS;
			AuditDataSet.GAS_GROUP_PRIVILEGERow groupPrivilegeRow;
			AuditDataSet.GAS_GROUPSRow groupsRow;
			AuditDataSet.GAS_SOFTWARE_APPSRow softwareAppsRow;
			AuditDataSet.GAS_USER_GROUPSRow userGroupsRow;
			AuditDataSet.GAS_USERSRow userRow;
			DateTime nowDate;
			IDbAccess dbAccess;
			IDbConnection connection;
			IDataReader dataReader;

			auditDS = new AuditDataSet();
			nowDate = DateTime.Now;

			#region Create HOST_AUDIT row

			AuditDataSet.GAS_HOST_AUDITRow hostAudit;

			hostAudit = auditDS.GAS_HOST_AUDIT.NewGAS_HOST_AUDITRow();
			hostAudit.AUDIT_ID = -1; //The web service changes it later
			hostAudit.HOST_ID = System.Net.Dns.GetHostName();
			hostAudit.AUDIT_DATETIME = nowDate;
			auditDS.GAS_HOST_AUDIT.AddGAS_HOST_AUDITRow(hostAudit);

			#endregion

			dbAccess = new OracleMicrosoftAccess(ConfigurationManager.AppSettings["OracleConnectionString"]);
			connection = dbAccess.GetConnection();

			connection.Open();

			#region Users and Groups

			if (auditOptions.Contains(GASConstants.GAS_AUDIT_OPTION_USERS_AND_GROUPS))
			{
				Console.WriteLine("Before generating Users and Groups");
				try
				{
					// GAS_USERS: DBA_USERS
					dataReader = dbAccess.ExecuteDataReader(connection, "SELECT * FROM DBA_USERS");
					while (dataReader.Read())
					{
						userRow = auditDS.GAS_USERS.NewGAS_USERSRow();
						// USER_CODE
						userRow.USER_CODE = dataReader["USERNAME"].ToString();
						// ACCOUNT_STATUS
						userRow.ACCOUNT_STATUS = dataReader["ACCOUNT_STATUS"].ToString();
						// ACCOUNT_EXPIRES
						object expiryDate = dataReader["EXPIRY_DATE"];
						if (expiryDate.GetType() != typeof(DBNull))
							userRow.ACCOUNT_EXPIRES = 1;
						else
							userRow.ACCOUNT_EXPIRES = 0;
						// IS_SYSTEM_USER
						object defaultTablespace = dataReader["DEFAULT_TABLESPACE"];
						if (defaultTablespace.GetType() != typeof(DBNull))
						{
							if ((defaultTablespace.ToString() == "SYSTEM") || (defaultTablespace.ToString() == "SYSAUX"))
								userRow.IS_SYSTEM_USER = 1;
							else
								userRow.IS_SYSTEM_USER = 0;
						}
						else
							userRow.IS_SYSTEM_USER = 0;
						// HOST_AUDIT
						userRow.HOST_AUDIT = hostAudit.ID;
						auditDS.GAS_USERS.AddGAS_USERSRow(userRow);
					}

					// GAS_GROUPS: DBA_ROLES
					dataReader = dbAccess.ExecuteDataReader(connection, "SELECT * FROM DBA_ROLES");
					while (dataReader.Read())
					{
						groupsRow = auditDS.GAS_GROUPS.NewGAS_GROUPSRow();
						// GROUP_NAME
						groupsRow.GROUP_NAME = dataReader["ROLE"].ToString();
						// GROUP_TYPE
						object passwordRequired = dataReader["PASSWORD_REQUIRED"];
						if (passwordRequired.GetType() != typeof(DBNull))
						{
							if (passwordRequired.ToString() == "NO")
								groupsRow.GROUP_TYPE = "PASSWORD NOT REQUIRED";
							else
								groupsRow.GROUP_TYPE = "PASSWORD REQUIRED";
						}
						// HOST_AUDIT
						groupsRow.HOST_AUDIT = hostAudit.ID;
						auditDS.GAS_GROUPS.AddGAS_GROUPSRow(groupsRow);
					}

					// GAS_USER_GROUPS: DBA_ROLE_PRIVS
					dataReader = dbAccess.ExecuteDataReader(connection, "SELECT * FROM DBA_ROLE_PRIVS");
					while (dataReader.Read())
					{
						// GAS_USER
						string userCode = dataReader["GRANTEE"].ToString();
						// GAS_GROUP
						string groupName = dataReader["GRANTED_ROLE"].ToString();
						if (!string.IsNullOrEmpty(userCode) && !string.IsNullOrEmpty(groupName))
						{
							AuditDataSet.GAS_USERSRow[] users;
							AuditDataSet.GAS_GROUPSRow[] groups;

							users = (AuditDataSet.GAS_USERSRow[])auditDS.GAS_USERS.Select("USER_CODE = '" + userCode + "'");
							groups = (AuditDataSet.GAS_GROUPSRow[])auditDS.GAS_GROUPS.Select("GROUP_NAME = '" + groupName + "'");
							if (users.Length == 1 && groups.Length == 1)
							{
								userGroupsRow = auditDS.GAS_USER_GROUPS.NewGAS_USER_GROUPSRow();
								userGroupsRow.GAS_USER = users[0].ID;
								userGroupsRow.GAS_GROUP = groups[0].ID;
								auditDS.GAS_USER_GROUPS.AddGAS_USER_GROUPSRow(userGroupsRow);
							}
						}
					}

					// GAS_GROUP_PRIVILEGE: ROLE_TAB_PRIVS
					dataReader = dbAccess.ExecuteDataReader(connection, "SELECT * FROM ROLE_TAB_PRIVS");
					while (dataReader.Read())
					{
						// GAS_GROUP
						string groupName = dataReader["ROLE"].ToString();
						if (!string.IsNullOrEmpty(groupName))
						{
							AuditDataSet.GAS_GROUPSRow[] groups;

							groups = (AuditDataSet.GAS_GROUPSRow[])auditDS.GAS_GROUPS.Select("GROUP_NAME = '" + groupName + "'");

							if (groups.Length == 1)
							{
								groupPrivilegeRow = auditDS.GAS_GROUP_PRIVILEGE.NewGAS_GROUP_PRIVILEGERow();
								groupPrivilegeRow.GAS_GROUP = groups[0].ID;
								object columnName;
								columnName = dataReader["COLUMN_NAME"];
								if (columnName.GetType() != typeof(DBNull))
									groupPrivilegeRow.PRIVILEGE = "Table: " + dataReader["TABLE_NAME"].ToString() + "; Column: " + columnName + "; Privilege: " + dataReader["PRIVILEGE"].ToString() + "; Grantable: " + dataReader["GRANTABLE"].ToString();
								else
									groupPrivilegeRow.PRIVILEGE = "Table: " + dataReader["TABLE_NAME"].ToString() + "; Privilege: " + dataReader["PRIVILEGE"].ToString() + "; Grantable: " + dataReader["GRANTABLE"].ToString();
								// HOST_AUDIT
								auditDS.GAS_GROUP_PRIVILEGE.AddGAS_GROUP_PRIVILEGERow(groupPrivilegeRow);
							}
						}
					}
				}
				catch (Exception ex)
				{
					Console.WriteLine("Exception while getting information: " + ex.Message);
				}
				finally
				{
					connection.Close();
				}
			}
			Console.WriteLine("After generating Users and Groups");

			#endregion

			Console.WriteLine("Before generating Software");

			#region Software

			if (auditOptions.Contains(GASConstants.GAS_AUDIT_OPTION_SOFTWARE))
			{
				try
				{
					// GAS_SOFTWARE_APPS: PRODUCT_COMPONENT_VERSION
					dataReader = dbAccess.ExecuteDataReader(connection, "SELECT * FROM PRODUCT_COMPONENT_VERSION");
					while (dataReader.Read())
					{
						softwareAppsRow = auditDS.GAS_SOFTWARE_APPS.NewGAS_SOFTWARE_APPSRow();
						// NAME
						softwareAppsRow.NAME = dataReader["PRODUCT"].ToString();
						// DESCRIPTION
						softwareAppsRow.DESCRIPTION = "STATUS: " + dataReader["STATUS"].ToString();
						// VERSION
						softwareAppsRow.VERSION = dataReader["VERSION"].ToString();
						// HOST_AUDIT
						softwareAppsRow.HOST_AUDIT = hostAudit.ID;
						auditDS.GAS_SOFTWARE_APPS.AddGAS_SOFTWARE_APPSRow(softwareAppsRow);
					}
				}
				catch (Exception ex)
				{
					Console.WriteLine("Exception while getting information: " + ex.Message);
				}
				finally
				{
					connection.Close();
				}
			}

			#endregion

			Console.WriteLine("After generating Software");

			return auditDS;
		}
	}
}
